Last week

We learned 4 functions in the tidyr package:

function purpose
gather() Gather variable values spread across multiple columns
spread() Spread out observation values scattered across rows
separate() Split one column into two or more columns
unite() Collapse multiple columns into one column

(http://r4ds.had.co.nz/transform.html)

Last week

And we used these functions and a few from Week 4 to create this table:

variable disclosed non-disclosed
c.age 12.3 11.7
c.female 50.8 51.6
p.age 47.1 49.0
p.female 86.8 89.3

Objectives

Today we’ll focus on relational data. Some of today’s examples come from Wickham and Grolemund (2017). By the end of this session, you should be able to:

  • Understand what makes data “relational”
  • Perform inner and outer joins

Login to Duke’s Docker-ized version of RStudio Server

  • Login to your instance by going to https://vm-manage.oit.duke.edu/containers and entering your NetID.
  • Click on Docker
  • Click on RStudio
  • When RStudio loads, restart the R session (Ctrl/Cmd+Shift+F10), clear the console (Ctrl/Cmd+L), and clear your workspace

Open your project

Is your project still open? If not, click on the project icon to load it. (Don’t create a new one.)

Download and open the template

Run the following code in your console. Change products to your preferred subfolder.

  download.file("https://tinyurl.com/ybvgoovu", 
                destfile = "products/lab-w06.Rmd")

Don’t make this mistake

One of the core principles of tidy data is that each type of observational unit is a table. Here’s the messy example from last week:

A table for every level

Relational data

We often work with this type of relational data. Sometimes the data are nested (e.g., kids within families, classrooms within schools) and we have different tables with data on each level. Other projects might involve data that just happen to be split across multiple tables that we need to bring together. In both cases, the tables are relational.

Getting relational data into R

If you have related data stored in different files, you need to load the data into R and combine the data objects. That’s what we’ll do today.

If you have a relational database, you can use RStudio to connect to the database, query/analyze the data, and only import what you need into R.

nycflights13

We’ll follow the lead of Wickham and Grolemund (2017) and use the nycflights13 package to learn the basics of relational data. Take a look at 5 tibbles: flights, airlines, airports, planes, and weather.

  flights
## # A tibble: 336,776 x 19
##     year month   day dep_time sched_dep_time dep_delay arr_time
##    <int> <int> <int>    <int>          <int>     <dbl>    <int>
##  1  2013     1     1      517            515         2      830
##  2  2013     1     1      533            529         4      850
##  3  2013     1     1      542            540         2      923
##  4  2013     1     1      544            545        -1     1004
##  5  2013     1     1      554            600        -6      812
##  6  2013     1     1      554            558        -4      740
##  7  2013     1     1      555            600        -5      913
##  8  2013     1     1      557            600        -3      709
##  9  2013     1     1      557            600        -3      838
## 10  2013     1     1      558            600        -2      753
## # ... with 336,766 more rows, and 12 more variables: sched_arr_time <int>,
## #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
## #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
## #   minute <dbl>, time_hour <dttm>

nycflights13

Airline names by carrier code.

  airlines
## # A tibble: 16 x 2
##    carrier                        name
##      <chr>                       <chr>
##  1      9E           Endeavor Air Inc.
##  2      AA      American Airlines Inc.
##  3      AS        Alaska Airlines Inc.
##  4      B6             JetBlue Airways
##  5      DL        Delta Air Lines Inc.
##  6      EV    ExpressJet Airlines Inc.
##  7      F9      Frontier Airlines Inc.
##  8      FL AirTran Airways Corporation
##  9      HA      Hawaiian Airlines Inc.
## 10      MQ                   Envoy Air
## 11      OO       SkyWest Airlines Inc.
## 12      UA       United Air Lines Inc.
## 13      US             US Airways Inc.
## 14      VX              Virgin America
## 15      WN      Southwest Airlines Co.
## 16      YV          Mesa Airlines Inc.

nycflights13

Airport details by faa airport codes.

  airports
## # A tibble: 1,458 x 8
##      faa                           name      lat        lon   alt    tz
##    <chr>                          <chr>    <dbl>      <dbl> <int> <dbl>
##  1   04G              Lansdowne Airport 41.13047  -80.61958  1044    -5
##  2   06A  Moton Field Municipal Airport 32.46057  -85.68003   264    -6
##  3   06C            Schaumburg Regional 41.98934  -88.10124   801    -6
##  4   06N                Randall Airport 41.43191  -74.39156   523    -5
##  5   09J          Jekyll Island Airport 31.07447  -81.42778    11    -5
##  6   0A9 Elizabethton Municipal Airport 36.37122  -82.17342  1593    -5
##  7   0G6        Williams County Airport 41.46731  -84.50678   730    -5
##  8   0G7  Finger Lakes Regional Airport 42.88356  -76.78123   492    -5
##  9   0P2   Shoestring Aviation Airfield 39.79482  -76.64719  1000    -5
## 10   0S9          Jefferson County Intl 48.05381 -122.81064   108    -8
## # ... with 1,448 more rows, and 2 more variables: dst <chr>, tzone <chr>

nycflights13

Plane details by tailnum.

  planes
## # A tibble: 3,322 x 9
##    tailnum  year                    type     manufacturer     model
##      <chr> <int>                   <chr>            <chr>     <chr>
##  1  N10156  2004 Fixed wing multi engine          EMBRAER EMB-145XR
##  2  N102UW  1998 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  3  N103US  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  4  N104UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  5  N10575  2002 Fixed wing multi engine          EMBRAER EMB-145LR
##  6  N105UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  7  N107US  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  8  N108UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
##  9  N109UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
## 10  N110UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
## # ... with 3,312 more rows, and 4 more variables: engines <int>,
## #   seats <int>, speed <int>, engine <chr>

nycflights13

Weather details by airport (origin faa code) and day/hour.

  weather
## # A tibble: 26,130 x 15
##    origin  year month   day  hour  temp  dewp humid wind_dir wind_speed
##     <chr> <dbl> <dbl> <int> <int> <dbl> <dbl> <dbl>    <dbl>      <dbl>
##  1    EWR  2013     1     1     0 37.04 21.92 53.97      230   10.35702
##  2    EWR  2013     1     1     1 37.04 21.92 53.97      230   13.80936
##  3    EWR  2013     1     1     2 37.94 21.92 52.09      230   12.65858
##  4    EWR  2013     1     1     3 37.94 23.00 54.51      230   13.80936
##  5    EWR  2013     1     1     4 37.94 24.08 57.04      240   14.96014
##  6    EWR  2013     1     1     6 39.02 26.06 59.37      270   10.35702
##  7    EWR  2013     1     1     7 39.02 26.96 61.63      250    8.05546
##  8    EWR  2013     1     1     8 39.02 28.04 64.43      240   11.50780
##  9    EWR  2013     1     1     9 39.92 28.04 62.21      250   12.65858
## 10    EWR  2013     1     1    10 39.02 28.04 64.43      260   12.65858
## # ... with 26,120 more rows, and 5 more variables: wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, time_hour <dttm>

Focus on the key relationships

  • flights links to planes by tailnum
  • flights links to airlines by carrier
  • flights links to airports by origin and destination
  • flights links to weather by origin and the time variables

Joins

When you want to combine a pair of tables, x and y, to make a bigger (wider) table, you join them.

function behavior
inner_join() Keeps observations that appear in x AND y
left_join() Keeps keeps all observations in x
right_join() Keeps all observations in y
full_join() Keeps all observations in x OR y

left_, right_, and full_ joins are types of outer joins.

Joins

Simple example

We’ll create two data objects, x and y.

  x <- tribble(
    ~key, ~val_x,
       1, "x1",
       2, "x2",
       3, "x3"
  )
  y <- tribble(
    ~key, ~val_y,
       1, "y1",
       2, "y2",
       4, "y3"
  )

inner_join()

The inner_join() function keeps observations that appear in x AND y, so 1 and 2. Obs 3 is dropped from x, and Obs 4 is dropped from y.

  x %>% 
    inner_join(y, by = "key")
## # A tibble: 2 x 3
##     key val_x val_y
##   <dbl> <chr> <chr>
## 1     1    x1    y1
## 2     2    x2    y2

inner_join()

The inner_join() function keeps observations that appear in x AND y, so 1 and 2. Obs 3 is dropped from x, and Obs 4 is dropped from y.

left_join()

The left_join() function keeps keeps all observations in x. Obs 4 in y is dropped. There is no Obs 3 in y, but it appears in x.

  x %>% 
    left_join(y, by = "key")
## # A tibble: 3 x 3
##     key val_x val_y
##   <dbl> <chr> <chr>
## 1     1    x1    y1
## 2     2    x2    y2
## 3     3    x3  <NA>

left_join()

The left_join() function keeps keeps all observations in x. Obs 4 in y is dropped. There is no Obs 3 in y, but it appears in x.

right_join()

The right_join() function keeps keeps all observations in y. Obs 3 in x is dropped. There is no Obs 4 in x, but it appears in y.

  x %>% 
    right_join(y, by = "key")
## # A tibble: 3 x 3
##     key val_x val_y
##   <dbl> <chr> <chr>
## 1     1    x1    y1
## 2     2    x2    y2
## 3     4  <NA>    y3

right_join()

The right_join() function keeps keeps all observations in y. Obs 3 in x is dropped. There is no Obs 4 in x, but it appears in y.

full_join()

The full_join() function keeps keeps all observations in x OR y. No observations are dropped.

  x %>% 
    full_join(y, by = "key")
## # A tibble: 4 x 3
##     key val_x val_y
##   <dbl> <chr> <chr>
## 1     1    x1    y1
## 2     2    x2    y2
## 3     3    x3  <NA>
## 4     4  <NA>    y3

full_join()

The full_join() function keeps keeps all observations in x OR y. No observations are dropped.

left_join() flights

Let’s say we want to join a subset of the flights data called flights2 and the planes data by the variable tailnum that exists in both tables.

  flights2 %>% 
    left_join(planes, by = "tailnum")
## # A tibble: 336,776 x 16
##    year.x month   day  hour origin  dest tailnum carrier year.y
##     <int> <int> <int> <dbl>  <chr> <chr>   <chr>   <chr>  <int>
##  1   2013     1     1     5    EWR   IAH  N14228      UA   1999
##  2   2013     1     1     5    LGA   IAH  N24211      UA   1998
##  3   2013     1     1     5    JFK   MIA  N619AA      AA   1990
##  4   2013     1     1     5    JFK   BQN  N804JB      B6   2012
##  5   2013     1     1     6    LGA   ATL  N668DN      DL   1991
##  6   2013     1     1     5    EWR   ORD  N39463      UA   2012
##  7   2013     1     1     6    EWR   FLL  N516JB      B6   2000
##  8   2013     1     1     6    LGA   IAD  N829AS      EV   1998
##  9   2013     1     1     6    JFK   MCO  N593JB      B6   2004
## 10   2013     1     1     6    LGA   ORD  N3ALAA      AA     NA
## # ... with 336,766 more rows, and 7 more variables: type <chr>,
## #   manufacturer <chr>, model <chr>, engines <int>, seats <int>,
## #   speed <int>, engine <chr>

left_join() flights

Sometimes the keys have different names. Here we join flights2 with airports by the airport code. In flights2, the airport code is in a variable called origin. In airports, the code is in the variable called faa.

  flights2 %>% 
    left_join(airports, by = c("origin" = "faa"))
## # A tibble: 336,776 x 15
##     year month   day  hour origin  dest tailnum carrier
##    <int> <int> <int> <dbl>  <chr> <chr>   <chr>   <chr>
##  1  2013     1     1     5    EWR   IAH  N14228      UA
##  2  2013     1     1     5    LGA   IAH  N24211      UA
##  3  2013     1     1     5    JFK   MIA  N619AA      AA
##  4  2013     1     1     5    JFK   BQN  N804JB      B6
##  5  2013     1     1     6    LGA   ATL  N668DN      DL
##  6  2013     1     1     5    EWR   ORD  N39463      UA
##  7  2013     1     1     6    EWR   FLL  N516JB      B6
##  8  2013     1     1     6    LGA   IAD  N829AS      EV
##  9  2013     1     1     6    JFK   MCO  N593JB      B6
## 10  2013     1     1     6    LGA   ORD  N3ALAA      AA
## # ... with 336,766 more rows, and 7 more variables: name <chr>, lat <dbl>,
## #   lon <dbl>, alt <int>, tz <dbl>, dst <chr>, tzone <chr>

left_join() flights

If you don’t specify the by parameter, the default behavior is to match on all variables that appear in both tables. In this example, the following variables exist in both tables: year, month, day, hour and origin.

  flights2 %>% 
    left_join(weather)
## Joining, by = c("year", "month", "day", "hour", "origin")
## # A tibble: 336,776 x 18
##     year month   day  hour origin  dest tailnum carrier  temp  dewp humid
##    <dbl> <dbl> <int> <dbl>  <chr> <chr>   <chr>   <chr> <dbl> <dbl> <dbl>
##  1  2013     1     1     5    EWR   IAH  N14228      UA    NA    NA    NA
##  2  2013     1     1     5    LGA   IAH  N24211      UA    NA    NA    NA
##  3  2013     1     1     5    JFK   MIA  N619AA      AA    NA    NA    NA
##  4  2013     1     1     5    JFK   BQN  N804JB      B6    NA    NA    NA
##  5  2013     1     1     6    LGA   ATL  N668DN      DL 39.92 26.06 57.33
##  6  2013     1     1     5    EWR   ORD  N39463      UA    NA    NA    NA
##  7  2013     1     1     6    EWR   FLL  N516JB      B6 39.02 26.06 59.37
##  8  2013     1     1     6    LGA   IAD  N829AS      EV 39.92 26.06 57.33
##  9  2013     1     1     6    JFK   MCO  N593JB      B6 39.02 26.06 59.37
## 10  2013     1     1     6    LGA   ORD  N3ALAA      AA 39.92 26.06 57.33
## # ... with 336,766 more rows, and 7 more variables: wind_dir <dbl>,
## #   wind_speed <dbl>, wind_gust <dbl>, precip <dbl>, pressure <dbl>,
## #   visib <dbl>, time_hour <dttm>

Disclosure example

The template includes some code to download and load a few tables from the pediatric HIV disclosure study. Participants are nested in clinics, and clinics are nested in districts. Participants are also nested in time since this was a panel survey.

(To make things simple and prevent the possible identification of participants, the clinics and districts data are made up.)

Combine panel data

A common task is combining panel (longitudinal) datasets stored in different files. In this example, datR1 is a simplified version of the round 1 data, and datR2 contains round 2 data. We want to bring them together.

Similar, but not identical

Panel datasets should contain a lot of the same variables, but there might be some variables that only exist in some rounds.

names(datR1)
## [1] "ID"         "c.age"      "p.knowsHIV" "clinicID"
names(datR2)
## [1] "ID"         "p.knowsHIV"

Which join function will keep all observations?

full_join() works, but…

What’s the problem?

  datR1 %>%
    full_join(datR2, by="ID")
##      ID c.age p.knowsHIV.x clinicID p.knowsHIV.y
## 1   118     9            1        2           NA
## 2    60    15            1        2           NA
## 3   311    10            1        2           NA
## 4   347     9            0        2           NA
## 5   315    10            0        2            0
## 6    99    15            1        2           NA
## 7   146    11            0        2            0
## 8   339    13            1        2           NA
## 9   228    10            1        2           NA
## 10  368    12            1        2           NA
## 11   29    10            1        2           NA
## 12  286    15            1        7           NA
## 13  220    11            0        7            1
## 14  166    13            0        7            0
## 15  278    14            0        7            0
## 16  302    13            1        7           NA
## 17  361    14            1        7           NA
## 18  120    14            1        7           NA
## 19   70    14            0        7            0
## 20   91    15            1        7           NA
## 21  337    12            1        7           NA
## 22  335    14            1        7           NA
## 23   84    15            1        7           NA
## 24  272    15            1        7           NA
## 25  300    12            0        7            1
## 26  204    15            0        7            0
## 27  164    11            1        7           NA
## 28  345    13            0        7            1
## 29   80    14            0        7            0
## 30  333    10            0        7            1
## 31  161    12            0        7            1
## 32  156    15            1       19           NA
## 33  208    13            1       19           NA
## 34   39    13            0       19            0
## 35   95    12            1       19           NA
## 36  182    13            1       19           NA
## 37  299     9            0       19            1
## 38  243    13            1       19           NA
## 39  207    12            1       19           NA
## 40   18    11            1       19           NA
## 41  358    12            1       19           NA
## 42  143    15            1       10           NA
## 43   27    11            0       10           NA
## 44  115    10            1       10           NA
## 45  219    13            0       10            0
## 46  348    12            0       10            1
## 47  119    14            1       10           NA
## 48   47    10            1       10           NA
## 49   38    12            1       10           NA
## 50  314    13            0       10            1
## 51  134    15            1       10           NA
## 52   63    14            1       10           NA
## 53  110    13            1       10           NA
## 54  155    11            0       10            0
## 55  330    14            1       10           NA
## 56  171    13            0       10            0
## 57  316    13            1       10           NA
## 58  334    11            1       10           NA
## 59  197    14            0       10            1
## 60  267    11            1       10           NA
## 61  236    13            1       10           NA
## 62  281    15            1        3           NA
## 63  241    11            1        3           NA
## 64  298    12            1        3           NA
## 65  142    11            1        3           NA
## 66   56    13            1        3           NA
## 67  127    12            1        3           NA
## 68  179    13            1        3           NA
## 69  159    12            1        3           NA
## 70   46    10            0        3            1
## 71  308    13            1        3           NA
## 72  262    14            1        3           NA
## 73  215    13            1        3           NA
## 74  310    14            1        3           NA
## 75  158    13            0        3            0
## 76  291    14            1       15           NA
## 77  148    12            0       15            0
## 78   50    11            0       15            0
## 79    8    14            1       15           NA
## 80  214    13            0       15            0
## 81  251    12            1       15           NA
## 82  320    15            1       15           NA
## 83  296    11            0       15            1
## 84  113    14            1       15           NA
## 85   15    11            0       15            0
## 86  295    14            1       20           NA
## 87  371    10            0       20            0
## 88  324    12            0       20            0
## 89   31    13            0       20            0
## 90  133    14            1       20           NA
## 91  172    10            0       20            0
## 92   85    13            1       20           NA
## 93  175    14            1       20           NA
## 94  185     9            0       20           NA
## 95    5    12            1       20           NA
## 96   45    14            1       20           NA
## 97  217    11            0        6            0
## 98  349    11            1        6           NA
## 99   67    15            1        6           NA
## 100 370    13            0        6            0
## 101 130    11            1        6           NA
## 102 187    15            0        6            0
## 103 274    14            1        6           NA
## 104  61    11            0        6            0
## 105 279    15            1        6           NA
## 106 287     9            1        6           NA
## 107 209    10            1        1           NA
## 108 128    12            0        1            0
## 109 329    10            0        1            1
## 110 276    12            0        1            0
## 111 336    10            1        1           NA
## 112 108    11            0        1            0
## 113  77    14            0        1            0
## 114  82    12            1        1           NA
## 115 121    12            0        1            0
## 116 331    15            1        1           NA
## 117 141    11            1        1           NA
## 118  90    11            0        1            0
## 119 319    10            1        1           NA
## 120 317     9            0        1            1
## 121 354    14            1        1           NA
## 122 224    11            0        1            1
## 123 365    13            1        1           NA
## 124 246    13            0        1            1
## 125 275    12            0        1            1
## 126 283    12            1        1           NA
## 127 170    12            1        1           NA
## 128  89    13            1        1           NA
## 129  78    12            0        1            1
## 130 359    12            0       14            0
## 131  34    14            1       14           NA
## 132  36    10            1       14           NA
## 133 189    12            1       14           NA
## 134  17    12            1       14           NA
## 135  30    11            1       14           NA
## 136 364    10            0       14            0
## 137 203    11            1       14           NA
## 138   4    12            1       14           NA
## 139 322    13            1       14           NA
## 140 112    10            1       14           NA
## 141 304    11            1       14           NA
## 142  74    14            1       14           NA
## 143  66    12            0       14            1
## 144 160    10            1       14           NA
## 145 104    13            1       14           NA
## 146  19     9            0       14            0
## 147  12    14            1       14           NA
## 148 312    12            1       14           NA
## 149 258    11            1       14           NA
## 150 265    14            1       14           NA
## 151  81    14            1       14           NA
## 152 293    12            1       14           NA
## 153  87    10            1       14           NA
## 154 261    11            1       14           NA
## 155 366    12            0       14            1
## 156  71    11            0       14            1
## 157 282    11            0       13            1
## 158 105    13            0       13           NA
## 159 206    10            1       13           NA
## 160  57     9            1       13           NA
## 161 147    10            1       13           NA
## 162 124    13            1       13           NA
## 163 157    10            1       13           NA
## 164 194     9            0       13            0
## 165 109     9            1       13           NA
## 166   3    12            1       13           NA
## 167 101    12            1       13           NA
## 168  59    10            1       13           NA
## 169 289    11            0       13            0
## 170  43    13            1       17           NA
## 171 253    12            0       17            0
## 172 240    13            1       17           NA
## 173 137    15            0       17            1
## 174 372    14            1       17           NA
## 175  20    12            0       17            0
## 176 290    10            1       17           NA
## 177 263    13            1       17           NA
## 178  22    10            1       17           NA
## 179  75    10            0       17            0
## 180 309    13            1       17           NA
## 181 284    11            0       17            0
## 182 162    12            1       17           NA
## 183 102    13            1       17           NA
## 184 285    12            1       18           NA
## 185 373    13            1       18           NA
## 186  92     9            0       18            0
## 187 344    13            1       18           NA
## 188 227    13            1       18           NA
## 189 186    13            0       18            0
## 190 136    11            1       18           NA
## 191 232    12            1       18           NA
## 192 297    11            0       18           NA
## 193 351    14            1       18           NA
## 194 346    13            0       18            0
## 195 149    12            1       18           NA
## 196 180    14            0       18           NA
## 197 239    13            1       18           NA
## 198 307    10            1       18           NA
## 199  23    10            1       18           NA
## 200 280    11            1        4           NA
## 201   2    10            1        4           NA
## 202 165    12            1        4           NA
## 203 114    14            1        4           NA
## 204 242    12            1        4           NA
## 205 362    14            0        4            1
## 206 177     9            0        4            0
## 207 271     9            1        4           NA
## 208 117    13            1        4           NA
## 209 235    11            1        4           NA
## 210   1    14            0        4            0
## 211 163    12            1        4           NA
## 212 255    15            1        4           NA
## 213  73     9            0        4            0
## 214 223    11            1        4           NA
## 215 252    11            1        4           NA
## 216 106    11            0       12            0
## 217  88    14            1       12           NA
## 218  51    14            1       12           NA
## 219  52     9            1       12           NA
## 220 196    13            1       12           NA
## 221 350    14            1       12           NA
## 222 191     9            1       12           NA
## 223 260    14            0       12           NA
## 224  97    14            1       12           NA
## 225 352    10            1       12           NA
## 226 360    11            1       12           NA
## 227 116    10            1       12           NA
## 228 173    12            1        9           NA
## 229 221    11            1        9           NA
## 230  76    14            0        9            1
## 231  93    15            1        9           NA
## 232  10    12            1        9           NA
## 233 257    13            1        9           NA
## 234 139    11            1        9           NA
## 235 111     9            0        9           NA
## 236 254    13            0        9            0
## 237   7    14            1        9           NA
## 238  86    13            1        9           NA
## 239 199    13            1        9           NA
## 240 168    13            1        9           NA
## 241  48    14            1        9           NA
## 242  14    12            1        9           NA
## 243 355    14            1        9           NA
## 244 126    14            1        9           NA
## 245 343    14            1        9           NA
## 246 321    11            1        9           NA
## 247 152    10            1        9           NA
## 248  72    13            0        9            1
## 249 357    10            1        9           NA
## 250 216    13            1        9           NA
## 251 181     9            0        9           NA
## 252  53    10            0        9            0
## 253 303    15            1        9           NA
## 254 369     9            0        9            0
## 255  96     9            1        9           NA
## 256  24    13            1        9           NA
## 257 288    15            1        9           NA
## 258 328    12            1       21           NA
## 259 313    11            1       21           NA
## 260 306    14            1       21           NA
## 261 237     9            0       21            1
## 262 318    11            1       21           NA
## 263 356    14            1       21           NA
## 264 247    15            0       21            0
## 265 202    10            0       21            0
## 266  13    12            0       21            0
## 267  49    10            1       21           NA
## 268 325    14            1       21           NA
## 269 122    13            1       21           NA
## 270 176     9            0       21            0
## 271  25    10            1       21           NA
## 272 129     9            1        8           NA
## 273 248    11            1        8           NA
## 274 174    13            0        8            0
## 275 269    10            0        8            0
## 276 200    13            1        8           NA
## 277 100    15            1        8           NA
## 278  54    11            1        8           NA
## 279 250    14            1        8           NA
## 280 132    13            1        8           NA
## 281 188    12            0        8            1
## 282 135    14            1        8           NA
## 283 305    12            1        8           NA
## 284 229    13            1        8           NA
## 285 184    11            1        8           NA
## 286 327    13            0        8            0
## 287 140    15            0       22            0
## 288 338    10            0       22            0
## 289  37    13            1       22           NA
## 290  98    14            0       22            1
## 291  26    11            1       22           NA
## 292  94    11            1       22           NA
## 293 183    13            1       22           NA
## 294  69     9            1       22           NA
## 295 218    14            1       22           NA
## 296   9    14            1       22           NA
## 297 205    13            0       22            1
## 298 144    12            1       11           NA
## 299 245    13            0       11            1
## 300 145    13            1       11           NA
## 301 151    15            1       11           NA
## 302  42    11            1       11           NA
## 303  62     9            1       11           NA
## 304 192    11            1       11           NA
## 305  11    15            0       11            1
## 306 332    14            1       11           NA
## 307 323     9            1       11           NA
## 308 103    14            1       11           NA
## 309 266    11            1       11           NA
## 310 341    15            1       11           NA
## 311 201    14            1       11           NA
## 312 178    13            1       11           NA
## 313 256    12            1       11           NA
## 314 238     9            1       11           NA
## 315  41    14            1       11           NA
## 316 353    12            1       11           NA
## 317 249    11            0       11           NA
## 318 211    11            0       11            0
## 319 367    11            0       11            1
## 320 225    15            1       11           NA
## 321 226    14            0       11            1
## 322 138    10            1       11           NA
## 323 326    11            1       11           NA
## 324  33    10            1       11           NA
## 325 244     9            0       11            0
## 326 264     9            0       11            1
## 327 294    14            1       11           NA
## 328  58    13            1       11           NA
## 329 273    11            0       11            0
## 330  79    15            1       11           NA
## 331 198     9            0       11            1
## 332  55    11            0       11            1
## 333  65    14            0       11            0
## 334 301     9            0       11            0
## 335 107    11            1       11           NA
## 336  83     9            1       11           NA
## 337 131    10            1       16           NA
## 338 234    12            1       16           NA
## 339 342    13            0       16            0
## 340 210    11            1       16           NA
## 341 268    13            1       16           NA
## 342 212    14            1       16           NA
## 343 123    15            1       16           NA
## 344  16    12            0       16            1
## 345 154    12            1       16           NA
## 346  44     9            0       16            1
## 347 277    11            1       16           NA
## 348 230    12            0       16            1
## 349  40    11            1       16           NA
## 350 363    12            0       16           NA
## 351  28    15            0       16            0
## 352 213    12            0       16            0
## 353 153    13            1       16           NA
## 354 195    11            0       16            1
## 355 292    15            0       16            0
## 356 125    13            1       16           NA
## 357 222    13            1       16           NA
## 358 259    10            0       16            0
## 359 340    14            1       16           NA
## 360 167    13            1       16           NA
## 361  21    14            1       16           NA
## 362 190    11            1       16           NA
## 363  35    10            0       16            0
## 364 150    13            1       16           NA
## 365 233    10            0       16            1
## 366 193    14            0       16            0
## 367 169    15            1       16           NA
## 368   6    13            1       16           NA
## 369 231    12            1       16           NA
## 370  32    13            1       16           NA
## 371  68    13            0       16            0
## 372 270    11            1       16           NA

p.knowsHIV exists in both tables

And therefore appears twice with the suffix .x and .y.

  datR1 %>%
    full_join(datR2, by="ID")
##      ID c.age p.knowsHIV.x clinicID p.knowsHIV.y
## 1   118     9            1        2           NA
## 2    60    15            1        2           NA
## 3   311    10            1        2           NA
## 4   347     9            0        2           NA
## 5   315    10            0        2            0
## 6    99    15            1        2           NA
## 7   146    11            0        2            0
## 8   339    13            1        2           NA
## 9   228    10            1        2           NA
## 10  368    12            1        2           NA
## 11   29    10            1        2           NA
## 12  286    15            1        7           NA
## 13  220    11            0        7            1
## 14  166    13            0        7            0
## 15  278    14            0        7            0
## 16  302    13            1        7           NA
## 17  361    14            1        7           NA
## 18  120    14            1        7           NA
## 19   70    14            0        7            0
## 20   91    15            1        7           NA
## 21  337    12            1        7           NA
## 22  335    14            1        7           NA
## 23   84    15            1        7           NA
## 24  272    15            1        7           NA
## 25  300    12            0        7            1
## 26  204    15            0        7            0
## 27  164    11            1        7           NA
## 28  345    13            0        7            1
## 29   80    14            0        7            0
## 30  333    10            0        7            1
## 31  161    12            0        7            1
## 32  156    15            1       19           NA
## 33  208    13            1       19           NA
## 34   39    13            0       19            0
## 35   95    12            1       19           NA
## 36  182    13            1       19           NA
## 37  299     9            0       19            1
## 38  243    13            1       19           NA
## 39  207    12            1       19           NA
## 40   18    11            1       19           NA
## 41  358    12            1       19           NA
## 42  143    15            1       10           NA
## 43   27    11            0       10           NA
## 44  115    10            1       10           NA
## 45  219    13            0       10            0
## 46  348    12            0       10            1
## 47  119    14            1       10           NA
## 48   47    10            1       10           NA
## 49   38    12            1       10           NA
## 50  314    13            0       10            1
## 51  134    15            1       10           NA
## 52   63    14            1       10           NA
## 53  110    13            1       10           NA
## 54  155    11            0       10            0
## 55  330    14            1       10           NA
## 56  171    13            0       10            0
## 57  316    13            1       10           NA
## 58  334    11            1       10           NA
## 59  197    14            0       10            1
## 60  267    11            1       10           NA
## 61  236    13            1       10           NA
## 62  281    15            1        3           NA
## 63  241    11            1        3           NA
## 64  298    12            1        3           NA
## 65  142    11            1        3           NA
## 66   56    13            1        3           NA
## 67  127    12            1        3           NA
## 68  179    13            1        3           NA
## 69  159    12            1        3           NA
## 70   46    10            0        3            1
## 71  308    13            1        3           NA
## 72  262    14            1        3           NA
## 73  215    13            1        3           NA
## 74  310    14            1        3           NA
## 75  158    13            0        3            0
## 76  291    14            1       15           NA
## 77  148    12            0       15            0
## 78   50    11            0       15            0
## 79    8    14            1       15           NA
## 80  214    13            0       15            0
## 81  251    12            1       15           NA
## 82  320    15            1       15           NA
## 83  296    11            0       15            1
## 84  113    14            1       15           NA
## 85   15    11            0       15            0
## 86  295    14            1       20           NA
## 87  371    10            0       20            0
## 88  324    12            0       20            0
## 89   31    13            0       20            0
## 90  133    14            1       20           NA
## 91  172    10            0       20            0
## 92   85    13            1       20           NA
## 93  175    14            1       20           NA
## 94  185     9            0       20           NA
## 95    5    12            1       20           NA
## 96   45    14            1       20           NA
## 97  217    11            0        6            0
## 98  349    11            1        6           NA
## 99   67    15            1        6           NA
## 100 370    13            0        6            0
## 101 130    11            1        6           NA
## 102 187    15            0        6            0
## 103 274    14            1        6           NA
## 104  61    11            0        6            0
## 105 279    15            1        6           NA
## 106 287     9            1        6           NA
## 107 209    10            1        1           NA
## 108 128    12            0        1            0
## 109 329    10            0        1            1
## 110 276    12            0        1            0
## 111 336    10            1        1           NA
## 112 108    11            0        1            0
## 113  77    14            0        1            0
## 114  82    12            1        1           NA
## 115 121    12            0        1            0
## 116 331    15            1        1           NA
## 117 141    11            1        1           NA
## 118  90    11            0        1            0
## 119 319    10            1        1           NA
## 120 317     9            0        1            1
## 121 354    14            1        1           NA
## 122 224    11            0        1            1
## 123 365    13            1        1           NA
## 124 246    13            0        1            1
## 125 275    12            0        1            1
## 126 283    12            1        1           NA
## 127 170    12            1        1           NA
## 128  89    13            1        1           NA
## 129  78    12            0        1            1
## 130 359    12            0       14            0
## 131  34    14            1       14           NA
## 132  36    10            1       14           NA
## 133 189    12            1       14           NA
## 134  17    12            1       14           NA
## 135  30    11            1       14           NA
## 136 364    10            0       14            0
## 137 203    11            1       14           NA
## 138   4    12            1       14           NA
## 139 322    13            1       14           NA
## 140 112    10            1       14           NA
## 141 304    11            1       14           NA
## 142  74    14            1       14           NA
## 143  66    12            0       14            1
## 144 160    10            1       14           NA
## 145 104    13            1       14           NA
## 146  19     9            0       14            0
## 147  12    14            1       14           NA
## 148 312    12            1       14           NA
## 149 258    11            1       14           NA
## 150 265    14            1       14           NA
## 151  81    14            1       14           NA
## 152 293    12            1       14           NA
## 153  87    10            1       14           NA
## 154 261    11            1       14           NA
## 155 366    12            0       14            1
## 156  71    11            0       14            1
## 157 282    11            0       13            1
## 158 105    13            0       13           NA
## 159 206    10            1       13           NA
## 160  57     9            1       13           NA
## 161 147    10            1       13           NA
## 162 124    13            1       13           NA
## 163 157    10            1       13           NA
## 164 194     9            0       13            0
## 165 109     9            1       13           NA
## 166   3    12            1       13           NA
## 167 101    12            1       13           NA
## 168  59    10            1       13           NA
## 169 289    11            0       13            0
## 170  43    13            1       17           NA
## 171 253    12            0       17            0
## 172 240    13            1       17           NA
## 173 137    15            0       17            1
## 174 372    14            1       17           NA
## 175  20    12            0       17            0
## 176 290    10            1       17           NA
## 177 263    13            1       17           NA
## 178  22    10            1       17           NA
## 179  75    10            0       17            0
## 180 309    13            1       17           NA
## 181 284    11            0       17            0
## 182 162    12            1       17           NA
## 183 102    13            1       17           NA
## 184 285    12            1       18           NA
## 185 373    13            1       18           NA
## 186  92     9            0       18            0
## 187 344    13            1       18           NA
## 188 227    13            1       18           NA
## 189 186    13            0       18            0
## 190 136    11            1       18           NA
## 191 232    12            1       18           NA
## 192 297    11            0       18           NA
## 193 351    14            1       18           NA
## 194 346    13            0       18            0
## 195 149    12            1       18           NA
## 196 180    14            0       18           NA
## 197 239    13            1       18           NA
## 198 307    10            1       18           NA
## 199  23    10            1       18           NA
## 200 280    11            1        4           NA
## 201   2    10            1        4           NA
## 202 165    12            1        4           NA
## 203 114    14            1        4           NA
## 204 242    12            1        4           NA
## 205 362    14            0        4            1
## 206 177     9            0        4            0
## 207 271     9            1        4           NA
## 208 117    13            1        4           NA
## 209 235    11            1        4           NA
## 210   1    14            0        4            0
## 211 163    12            1        4           NA
## 212 255    15            1        4           NA
## 213  73     9            0        4            0
## 214 223    11            1        4           NA
## 215 252    11            1        4           NA
## 216 106    11            0       12            0
## 217  88    14            1       12           NA
## 218  51    14            1       12           NA
## 219  52     9            1       12           NA
## 220 196    13            1       12           NA
## 221 350    14            1       12           NA
## 222 191     9            1       12           NA
## 223 260    14            0       12           NA
## 224  97    14            1       12           NA
## 225 352    10            1       12           NA
## 226 360    11            1       12           NA
## 227 116    10            1       12           NA
## 228 173    12            1        9           NA
## 229 221    11            1        9           NA
## 230  76    14            0        9            1
## 231  93    15            1        9           NA
## 232  10    12            1        9           NA
## 233 257    13            1        9           NA
## 234 139    11            1        9           NA
## 235 111     9            0        9           NA
## 236 254    13            0        9            0
## 237   7    14            1        9           NA
## 238  86    13            1        9           NA
## 239 199    13            1        9           NA
## 240 168    13            1        9           NA
## 241  48    14            1        9           NA
## 242  14    12            1        9           NA
## 243 355    14            1        9           NA
## 244 126    14            1        9           NA
## 245 343    14            1        9           NA
## 246 321    11            1        9           NA
## 247 152    10            1        9           NA
## 248  72    13            0        9            1
## 249 357    10            1        9           NA
## 250 216    13            1        9           NA
## 251 181     9            0        9           NA
## 252  53    10            0        9            0
## 253 303    15            1        9           NA
## 254 369     9            0        9            0
## 255  96     9            1        9           NA
## 256  24    13            1        9           NA
## 257 288    15            1        9           NA
## 258 328    12            1       21           NA
## 259 313    11            1       21           NA
## 260 306    14            1       21           NA
## 261 237     9            0       21            1
## 262 318    11            1       21           NA
## 263 356    14            1       21           NA
## 264 247    15            0       21            0
## 265 202    10            0       21            0
## 266  13    12            0       21            0
## 267  49    10            1       21           NA
## 268 325    14            1       21           NA
## 269 122    13            1       21           NA
## 270 176     9            0       21            0
## 271  25    10            1       21           NA
## 272 129     9            1        8           NA
## 273 248    11            1        8           NA
## 274 174    13            0        8            0
## 275 269    10            0        8            0
## 276 200    13            1        8           NA
## 277 100    15            1        8           NA
## 278  54    11            1        8           NA
## 279 250    14            1        8           NA
## 280 132    13            1        8           NA
## 281 188    12            0        8            1
## 282 135    14            1        8           NA
## 283 305    12            1        8           NA
## 284 229    13            1        8           NA
## 285 184    11            1        8           NA
## 286 327    13            0        8            0
## 287 140    15            0       22            0
## 288 338    10            0       22            0
## 289  37    13            1       22           NA
## 290  98    14            0       22            1
## 291  26    11            1       22           NA
## 292  94    11            1       22           NA
## 293 183    13            1       22           NA
## 294  69     9            1       22           NA
## 295 218    14            1       22           NA
## 296   9    14            1       22           NA
## 297 205    13            0       22            1
## 298 144    12            1       11           NA
## 299 245    13            0       11            1
## 300 145    13            1       11           NA
## 301 151    15            1       11           NA
## 302  42    11            1       11           NA
## 303  62     9            1       11           NA
## 304 192    11            1       11           NA
## 305  11    15            0       11            1
## 306 332    14            1       11           NA
## 307 323     9            1       11           NA
## 308 103    14            1       11           NA
## 309 266    11            1       11           NA
## 310 341    15            1       11           NA
## 311 201    14            1       11           NA
## 312 178    13            1       11           NA
## 313 256    12            1       11           NA
## 314 238     9            1       11           NA
## 315  41    14            1       11           NA
## 316 353    12            1       11           NA
## 317 249    11            0       11           NA
## 318 211    11            0       11            0
## 319 367    11            0       11            1
## 320 225    15            1       11           NA
## 321 226    14            0       11            1
## 322 138    10            1       11           NA
## 323 326    11            1       11           NA
## 324  33    10            1       11           NA
## 325 244     9            0       11            0
## 326 264     9            0       11            1
## 327 294    14            1       11           NA
## 328  58    13            1       11           NA
## 329 273    11            0       11            0
## 330  79    15            1       11           NA
## 331 198     9            0       11            1
## 332  55    11            0       11            1
## 333  65    14            0       11            0
## 334 301     9            0       11            0
## 335 107    11            1       11           NA
## 336  83     9            1       11           NA
## 337 131    10            1       16           NA
## 338 234    12            1       16           NA
## 339 342    13            0       16            0
## 340 210    11            1       16           NA
## 341 268    13            1       16           NA
## 342 212    14            1       16           NA
## 343 123    15            1       16           NA
## 344  16    12            0       16            1
## 345 154    12            1       16           NA
## 346  44     9            0       16            1
## 347 277    11            1       16           NA
## 348 230    12            0       16            1
## 349  40    11            1       16           NA
## 350 363    12            0       16           NA
## 351  28    15            0       16            0
## 352 213    12            0       16            0
## 353 153    13            1       16           NA
## 354 195    11            0       16            1
## 355 292    15            0       16            0
## 356 125    13            1       16           NA
## 357 222    13            1       16           NA
## 358 259    10            0       16            0
## 359 340    14            1       16           NA
## 360 167    13            1       16           NA
## 361  21    14            1       16           NA
## 362 190    11            1       16           NA
## 363  35    10            0       16            0
## 364 150    13            1       16           NA
## 365 233    10            0       16            1
## 366 193    14            0       16            0
## 367 169    15            1       16           NA
## 368   6    13            1       16           NA
## 369 231    12            1       16           NA
## 370  32    13            1       16           NA
## 371  68    13            0       16            0
## 372 270    11            1       16           NA

Use the suffix argument

  datR1 %>%
    full_join(datR2, 
              by = "ID", 
              suffix = c(".r1", ".r2"))
##      ID c.age p.knowsHIV.r1 clinicID p.knowsHIV.r2
## 1   118     9             1        2            NA
## 2    60    15             1        2            NA
## 3   311    10             1        2            NA
## 4   347     9             0        2            NA
## 5   315    10             0        2             0
## 6    99    15             1        2            NA
## 7   146    11             0        2             0
## 8   339    13             1        2            NA
## 9   228    10             1        2            NA
## 10  368    12             1        2            NA
## 11   29    10             1        2            NA
## 12  286    15             1        7            NA
## 13  220    11             0        7             1
## 14  166    13             0        7             0
## 15  278    14             0        7             0
## 16  302    13             1        7            NA
## 17  361    14             1        7            NA
## 18  120    14             1        7            NA
## 19   70    14             0        7             0
## 20   91    15             1        7            NA
## 21  337    12             1        7            NA
## 22  335    14             1        7            NA
## 23   84    15             1        7            NA
## 24  272    15             1        7            NA
## 25  300    12             0        7             1
## 26  204    15             0        7             0
## 27  164    11             1        7            NA
## 28  345    13             0        7             1
## 29   80    14             0        7             0
## 30  333    10             0        7             1
## 31  161    12             0        7             1
## 32  156    15             1       19            NA
## 33  208    13             1       19            NA
## 34   39    13             0       19             0
## 35   95    12             1       19            NA
## 36  182    13             1       19            NA
## 37  299     9             0       19             1
## 38  243    13             1       19            NA
## 39  207    12             1       19            NA
## 40   18    11             1       19            NA
## 41  358    12             1       19            NA
## 42  143    15             1       10            NA
## 43   27    11             0       10            NA
## 44  115    10             1       10            NA
## 45  219    13             0       10             0
## 46  348    12             0       10             1
## 47  119    14             1       10            NA
## 48   47    10             1       10            NA
## 49   38    12             1       10            NA
## 50  314    13             0       10             1
## 51  134    15             1       10            NA
## 52   63    14             1       10            NA
## 53  110    13             1       10            NA
## 54  155    11             0       10             0
## 55  330    14             1       10            NA
## 56  171    13             0       10             0
## 57  316    13             1       10            NA
## 58  334    11             1       10            NA
## 59  197    14             0       10             1
## 60  267    11             1       10            NA
## 61  236    13             1       10            NA
## 62  281    15             1        3            NA
## 63  241    11             1        3            NA
## 64  298    12             1        3            NA
## 65  142    11             1        3            NA
## 66   56    13             1        3            NA
## 67  127    12             1        3            NA
## 68  179    13             1        3            NA
## 69  159    12             1        3            NA
## 70   46    10             0        3             1
## 71  308    13             1        3            NA
## 72  262    14             1        3            NA
## 73  215    13             1        3            NA
## 74  310    14             1        3            NA
## 75  158    13             0        3             0
## 76  291    14             1       15            NA
## 77  148    12             0       15             0
## 78   50    11             0       15             0
## 79    8    14             1       15            NA
## 80  214    13             0       15             0
## 81  251    12             1       15            NA
## 82  320    15             1       15            NA
## 83  296    11             0       15             1
## 84  113    14             1       15            NA
## 85   15    11             0       15             0
## 86  295    14             1       20            NA
## 87  371    10             0       20             0
## 88  324    12             0       20             0
## 89   31    13             0       20             0
## 90  133    14             1       20            NA
## 91  172    10             0       20             0
## 92   85    13             1       20            NA
## 93  175    14             1       20            NA
## 94  185     9             0       20            NA
## 95    5    12             1       20            NA
## 96   45    14             1       20            NA
## 97  217    11             0        6             0
## 98  349    11             1        6            NA
## 99   67    15             1        6            NA
## 100 370    13             0        6             0
## 101 130    11             1        6            NA
## 102 187    15             0        6             0
## 103 274    14             1        6            NA
## 104  61    11             0        6             0
## 105 279    15             1        6            NA
## 106 287     9             1        6            NA
## 107 209    10             1        1            NA
## 108 128    12             0        1             0
## 109 329    10             0        1             1
## 110 276    12             0        1             0
## 111 336    10             1        1            NA
## 112 108    11             0        1             0
## 113  77    14             0        1             0
## 114  82    12             1        1            NA
## 115 121    12             0        1             0
## 116 331    15             1        1            NA
## 117 141    11             1        1            NA
## 118  90    11             0        1             0
## 119 319    10             1        1            NA
## 120 317     9             0        1             1
## 121 354    14             1        1            NA
## 122 224    11             0        1             1
## 123 365    13             1        1            NA
## 124 246    13             0        1             1
## 125 275    12             0        1             1
## 126 283    12             1        1            NA
## 127 170    12             1        1            NA
## 128  89    13             1        1            NA
## 129  78    12             0        1             1
## 130 359    12             0       14             0
## 131  34    14             1       14            NA
## 132  36    10             1       14            NA
## 133 189    12             1       14            NA
## 134  17    12             1       14            NA
## 135  30    11             1       14            NA
## 136 364    10             0       14             0
## 137 203    11             1       14            NA
## 138   4    12             1       14            NA
## 139 322    13             1       14            NA
## 140 112    10             1       14            NA
## 141 304    11             1       14            NA
## 142  74    14             1       14            NA
## 143  66    12             0       14             1
## 144 160    10             1       14            NA
## 145 104    13             1       14            NA
## 146  19     9             0       14             0
## 147  12    14             1       14            NA
## 148 312    12             1       14            NA
## 149 258    11             1       14            NA
## 150 265    14             1       14            NA
## 151  81    14             1       14            NA
## 152 293    12             1       14            NA
## 153  87    10             1       14            NA
## 154 261    11             1       14            NA
## 155 366    12             0       14             1
## 156  71    11             0       14             1
## 157 282    11             0       13             1
## 158 105    13             0       13            NA
## 159 206    10             1       13            NA
## 160  57     9             1       13            NA
## 161 147    10             1       13            NA
## 162 124    13             1       13            NA
## 163 157    10             1       13            NA
## 164 194     9             0       13             0
## 165 109     9             1       13            NA
## 166   3    12             1       13            NA
## 167 101    12             1       13            NA
## 168  59    10             1       13            NA
## 169 289    11             0       13             0
## 170  43    13             1       17            NA
## 171 253    12             0       17             0
## 172 240    13             1       17            NA
## 173 137    15             0       17             1
## 174 372    14             1       17            NA
## 175  20    12             0       17             0
## 176 290    10             1       17            NA
## 177 263    13             1       17            NA
## 178  22    10             1       17            NA
## 179  75    10             0       17             0
## 180 309    13             1       17            NA
## 181 284    11             0       17             0
## 182 162    12             1       17            NA
## 183 102    13             1       17            NA
## 184 285    12             1       18            NA
## 185 373    13             1       18            NA
## 186  92     9             0       18             0
## 187 344    13             1       18            NA
## 188 227    13             1       18            NA
## 189 186    13             0       18             0
## 190 136    11             1       18            NA
## 191 232    12             1       18            NA
## 192 297    11             0       18            NA
## 193 351    14             1       18            NA
## 194 346    13             0       18             0
## 195 149    12             1       18            NA
## 196 180    14             0       18            NA
## 197 239    13             1       18            NA
## 198 307    10             1       18            NA
## 199  23    10             1       18            NA
## 200 280    11             1        4            NA
## 201   2    10             1        4            NA
## 202 165    12             1        4            NA
## 203 114    14             1        4            NA
## 204 242    12             1        4            NA
## 205 362    14             0        4             1
## 206 177     9             0        4             0
## 207 271     9             1        4            NA
## 208 117    13             1        4            NA
## 209 235    11             1        4            NA
## 210   1    14             0        4             0
## 211 163    12             1        4            NA
## 212 255    15             1        4            NA
## 213  73     9             0        4             0
## 214 223    11             1        4            NA
## 215 252    11             1        4            NA
## 216 106    11             0       12             0
## 217  88    14             1       12            NA
## 218  51    14             1       12            NA
## 219  52     9             1       12            NA
## 220 196    13             1       12            NA
## 221 350    14             1       12            NA
## 222 191     9             1       12            NA
## 223 260    14             0       12            NA
## 224  97    14             1       12            NA
## 225 352    10             1       12            NA
## 226 360    11             1       12            NA
## 227 116    10             1       12            NA
## 228 173    12             1        9            NA
## 229 221    11             1        9            NA
## 230  76    14             0        9             1
## 231  93    15             1        9            NA
## 232  10    12             1        9            NA
## 233 257    13             1        9            NA
## 234 139    11             1        9            NA
## 235 111     9             0        9            NA
## 236 254    13             0        9             0
## 237   7    14             1        9            NA
## 238  86    13             1        9            NA
## 239 199    13             1        9            NA
## 240 168    13             1        9            NA
## 241  48    14             1        9            NA
## 242  14    12             1        9            NA
## 243 355    14             1        9            NA
## 244 126    14             1        9            NA
## 245 343    14             1        9            NA
## 246 321    11             1        9            NA
## 247 152    10             1        9            NA
## 248  72    13             0        9             1
## 249 357    10             1        9            NA
## 250 216    13             1        9            NA
## 251 181     9             0        9            NA
## 252  53    10             0        9             0
## 253 303    15             1        9            NA
## 254 369     9             0        9             0
## 255  96     9             1        9            NA
## 256  24    13             1        9            NA
## 257 288    15             1        9            NA
## 258 328    12             1       21            NA
## 259 313    11             1       21            NA
## 260 306    14             1       21            NA
## 261 237     9             0       21             1
## 262 318    11             1       21            NA
## 263 356    14             1       21            NA
## 264 247    15             0       21             0
## 265 202    10             0       21             0
## 266  13    12             0       21             0
## 267  49    10             1       21            NA
## 268 325    14             1       21            NA
## 269 122    13             1       21            NA
## 270 176     9             0       21             0
## 271  25    10             1       21            NA
## 272 129     9             1        8            NA
## 273 248    11             1        8            NA
## 274 174    13             0        8             0
## 275 269    10             0        8             0
## 276 200    13             1        8            NA
## 277 100    15             1        8            NA
## 278  54    11             1        8            NA
## 279 250    14             1        8            NA
## 280 132    13             1        8            NA
## 281 188    12             0        8             1
## 282 135    14             1        8            NA
## 283 305    12             1        8            NA
## 284 229    13             1        8            NA
## 285 184    11             1        8            NA
## 286 327    13             0        8             0
## 287 140    15             0       22             0
## 288 338    10             0       22             0
## 289  37    13             1       22            NA
## 290  98    14             0       22             1
## 291  26    11             1       22            NA
## 292  94    11             1       22            NA
## 293 183    13             1       22            NA
## 294  69     9             1       22            NA
## 295 218    14             1       22            NA
## 296   9    14             1       22            NA
## 297 205    13             0       22             1
## 298 144    12             1       11            NA
## 299 245    13             0       11             1
## 300 145    13             1       11            NA
## 301 151    15             1       11            NA
## 302  42    11             1       11            NA
## 303  62     9             1       11            NA
## 304 192    11             1       11            NA
## 305  11    15             0       11             1
## 306 332    14             1       11            NA
## 307 323     9             1       11            NA
## 308 103    14             1       11            NA
## 309 266    11             1       11            NA
## 310 341    15             1       11            NA
## 311 201    14             1       11            NA
## 312 178    13             1       11            NA
## 313 256    12             1       11            NA
## 314 238     9             1       11            NA
## 315  41    14             1       11            NA
## 316 353    12             1       11            NA
## 317 249    11             0       11            NA
## 318 211    11             0       11             0
## 319 367    11             0       11             1
## 320 225    15             1       11            NA
## 321 226    14             0       11             1
## 322 138    10             1       11            NA
## 323 326    11             1       11            NA
## 324  33    10             1       11            NA
## 325 244     9             0       11             0
## 326 264     9             0       11             1
## 327 294    14             1       11            NA
## 328  58    13             1       11            NA
## 329 273    11             0       11             0
## 330  79    15             1       11            NA
## 331 198     9             0       11             1
## 332  55    11             0       11             1
## 333  65    14             0       11             0
## 334 301     9             0       11             0
## 335 107    11             1       11            NA
## 336  83     9             1       11            NA
## 337 131    10             1       16            NA
## 338 234    12             1       16            NA
## 339 342    13             0       16             0
## 340 210    11             1       16            NA
## 341 268    13             1       16            NA
## 342 212    14             1       16            NA
## 343 123    15             1       16            NA
## 344  16    12             0       16             1
## 345 154    12             1       16            NA
## 346  44     9             0       16             1
## 347 277    11             1       16            NA
## 348 230    12             0       16             1
## 349  40    11             1       16            NA
## 350 363    12             0       16            NA
## 351  28    15             0       16             0
## 352 213    12             0       16             0
## 353 153    13             1       16            NA
## 354 195    11             0       16             1
## 355 292    15             0       16             0
## 356 125    13             1       16            NA
## 357 222    13             1       16            NA
## 358 259    10             0       16             0
## 359 340    14             1       16            NA
## 360 167    13             1       16            NA
## 361  21    14             1       16            NA
## 362 190    11             1       16            NA
## 363  35    10             0       16             0
## 364 150    13             1       16            NA
## 365 233    10             0       16             1
## 366 193    14             0       16             0
## 367 169    15             1       16            NA
## 368   6    13             1       16            NA
## 369 231    12             1       16            NA
## 370  32    13             1       16            NA
## 371  68    13             0       16             0
## 372 270    11             1       16            NA

A brief detour

My attempt

  r1 <- data.frame(id=c(1, 2, 3),
                   v1=c(1, 1, 0),
                   v2=c(0, 1, 1),
                   round=c(1, 1, 1))
  
  r2 <- data.frame(id=c(1, 2, 3),
                   v2=c(1, 0, 0),
                   round=c(2, 2, 2))
  
  r12 <- bind_rows(r1, r2)
  
  r12w <- reshape(r12,
                  timevar = "round",
                  v.names = "v2",
                  idvar = "id",
                  direction = "wide")
## Warning in reshapeWide(data, idvar = idvar, timevar = timevar, varying =
## varying, : some constant variables (v1) are really varying
  r12w
##   id v1 v2.1 v2.2
## 1  1  1    0    1
## 2  2  1    1    0
## 3  3  0    1    0

Some reactions

A promising idea

  r12 %>% 
    mutate(v1 = replace(v1, is.na(v1), v1[!is.na(v1)])) %>% 
    mutate(round = paste0("v2.", round)) %>% 
    spread(round, v2)
##   id v1 v2.1 v2.2
## 1  1  1    0    1
## 2  2  1    1    0
## 3  3  0    1    0

A new idea

  nm1 <- setdiff(names(r1), names(r2))
  r2[nm1] <- r1[nm1]
  bind_rows(r1, r2) %>% 
    mutate(round = paste0("v2.", round)) %>% 
    spread(round, v2)
##   id v1 v2.1 v2.2
## 1  1  1    0    1
## 2  2  1    1    0
## 3  3  0    1    0

Another wrinkle

An updated example

The Answer!

The Answer!

  full_join(r1, r2, by = "id", 
            suffix = c(".1", ".2")) %>%
    select(-starts_with("round"))
##   id v1 v2.1 v2.2
## 1  1  1    0    1
## 2  2  1    1    0
## 3  3  0    1    0
## 4  4  0    1   NA

Now join individuals to clinics

Now join individuals to clinics

  datR1 %>%
    full_join(datR2, 
              by = "ID", 
              suffix = c(".r1", ".r2")) %>%
    left_join(clinics, by="clinicID")
##      ID c.age p.knowsHIV.r1 clinicID p.knowsHIV.r2 district public
## 1   118     9             1        2            NA        A      1
## 2    60    15             1        2            NA        A      1
## 3   311    10             1        2            NA        A      1
## 4   347     9             0        2            NA        A      1
## 5   315    10             0        2             0        A      1
## 6    99    15             1        2            NA        A      1
## 7   146    11             0        2             0        A      1
## 8   339    13             1        2            NA        A      1
## 9   228    10             1        2            NA        A      1
## 10  368    12             1        2            NA        A      1
## 11   29    10             1        2            NA        A      1
## 12  286    15             1        7            NA        A      1
## 13  220    11             0        7             1        A      1
## 14  166    13             0        7             0        A      1
## 15  278    14             0        7             0        A      1
## 16  302    13             1        7            NA        A      1
## 17  361    14             1        7            NA        A      1
## 18  120    14             1        7            NA        A      1
## 19   70    14             0        7             0        A      1
## 20   91    15             1        7            NA        A      1
## 21  337    12             1        7            NA        A      1
## 22  335    14             1        7            NA        A      1
## 23   84    15             1        7            NA        A      1
## 24  272    15             1        7            NA        A      1
## 25  300    12             0        7             1        A      1
## 26  204    15             0        7             0        A      1
## 27  164    11             1        7            NA        A      1
## 28  345    13             0        7             1        A      1
## 29   80    14             0        7             0        A      1
## 30  333    10             0        7             1        A      1
## 31  161    12             0        7             1        A      1
## 32  156    15             1       19            NA        A      1
## 33  208    13             1       19            NA        A      1
## 34   39    13             0       19             0        A      1
## 35   95    12             1       19            NA        A      1
## 36  182    13             1       19            NA        A      1
## 37  299     9             0       19             1        A      1
## 38  243    13             1       19            NA        A      1
## 39  207    12             1       19            NA        A      1
## 40   18    11             1       19            NA        A      1
## 41  358    12             1       19            NA        A      1
## 42  143    15             1       10            NA        A      1
## 43   27    11             0       10            NA        A      1
## 44  115    10             1       10            NA        A      1
## 45  219    13             0       10             0        A      1
## 46  348    12             0       10             1        A      1
## 47  119    14             1       10            NA        A      1
## 48   47    10             1       10            NA        A      1
## 49   38    12             1       10            NA        A      1
## 50  314    13             0       10             1        A      1
## 51  134    15             1       10            NA        A      1
## 52   63    14             1       10            NA        A      1
## 53  110    13             1       10            NA        A      1
## 54  155    11             0       10             0        A      1
## 55  330    14             1       10            NA        A      1
## 56  171    13             0       10             0        A      1
## 57  316    13             1       10            NA        A      1
## 58  334    11             1       10            NA        A      1
## 59  197    14             0       10             1        A      1
## 60  267    11             1       10            NA        A      1
## 61  236    13             1       10            NA        A      1
## 62  281    15             1        3            NA        A      1
## 63  241    11             1        3            NA        A      1
## 64  298    12             1        3            NA        A      1
## 65  142    11             1        3            NA        A      1
## 66   56    13             1        3            NA        A      1
## 67  127    12             1        3            NA        A      1
## 68  179    13             1        3            NA        A      1
## 69  159    12             1        3            NA        A      1
## 70   46    10             0        3             1        A      1
## 71  308    13             1        3            NA        A      1
## 72  262    14             1        3            NA        A      1
## 73  215    13             1        3            NA        A      1
## 74  310    14             1        3            NA        A      1
## 75  158    13             0        3             0        A      1
## 76  291    14             1       15            NA        A      1
## 77  148    12             0       15             0        A      1
## 78   50    11             0       15             0        A      1
## 79    8    14             1       15            NA        A      1
## 80  214    13             0       15             0        A      1
## 81  251    12             1       15            NA        A      1
## 82  320    15             1       15            NA        A      1
## 83  296    11             0       15             1        A      1
## 84  113    14             1       15            NA        A      1
## 85   15    11             0       15             0        A      1
## 86  295    14             1       20            NA        A      0
## 87  371    10             0       20             0        A      0
## 88  324    12             0       20             0        A      0
## 89   31    13             0       20             0        A      0
## 90  133    14             1       20            NA        A      0
## 91  172    10             0       20             0        A      0
## 92   85    13             1       20            NA        A      0
## 93  175    14             1       20            NA        A      0
## 94  185     9             0       20            NA        A      0
## 95    5    12             1       20            NA        A      0
## 96   45    14             1       20            NA        A      0
## 97  217    11             0        6             0        A      0
## 98  349    11             1        6            NA        A      0
## 99   67    15             1        6            NA        A      0
## 100 370    13             0        6             0        A      0
## 101 130    11             1        6            NA        A      0
## 102 187    15             0        6             0        A      0
## 103 274    14             1        6            NA        A      0
## 104  61    11             0        6             0        A      0
## 105 279    15             1        6            NA        A      0
## 106 287     9             1        6            NA        A      0
## 107 209    10             1        1            NA        A      1
## 108 128    12             0        1             0        A      1
## 109 329    10             0        1             1        A      1
## 110 276    12             0        1             0        A      1
## 111 336    10             1        1            NA        A      1
## 112 108    11             0        1             0        A      1
## 113  77    14             0        1             0        A      1
## 114  82    12             1        1            NA        A      1
## 115 121    12             0        1             0        A      1
## 116 331    15             1        1            NA        A      1
## 117 141    11             1        1            NA        A      1
## 118  90    11             0        1             0        A      1
## 119 319    10             1        1            NA        A      1
## 120 317     9             0        1             1        A      1
## 121 354    14             1        1            NA        A      1
## 122 224    11             0        1             1        A      1
## 123 365    13             1        1            NA        A      1
## 124 246    13             0        1             1        A      1
## 125 275    12             0        1             1        A      1
## 126 283    12             1        1            NA        A      1
## 127 170    12             1        1            NA        A      1
## 128  89    13             1        1            NA        A      1
## 129  78    12             0        1             1        A      1
## 130 359    12             0       14             0        A      0
## 131  34    14             1       14            NA        A      0
## 132  36    10             1       14            NA        A      0
## 133 189    12             1       14            NA        A      0
## 134  17    12             1       14            NA        A      0
## 135  30    11             1       14            NA        A      0
## 136 364    10             0       14             0        A      0
## 137 203    11             1       14            NA        A      0
## 138   4    12             1       14            NA        A      0
## 139 322    13             1       14            NA        A      0
## 140 112    10             1       14            NA        A      0
## 141 304    11             1       14            NA        A      0
## 142  74    14             1       14            NA        A      0
## 143  66    12             0       14             1        A      0
## 144 160    10             1       14            NA        A      0
## 145 104    13             1       14            NA        A      0
## 146  19     9             0       14             0        A      0
## 147  12    14             1       14            NA        A      0
## 148 312    12             1       14            NA        A      0
## 149 258    11             1       14            NA        A      0
## 150 265    14             1       14            NA        A      0
## 151  81    14             1       14            NA        A      0
## 152 293    12             1       14            NA        A      0
## 153  87    10             1       14            NA        A      0
## 154 261    11             1       14            NA        A      0
## 155 366    12             0       14             1        A      0
## 156  71    11             0       14             1        A      0
## 157 282    11             0       13             1        B      1
## 158 105    13             0       13            NA        B      1
## 159 206    10             1       13            NA        B      1
## 160  57     9             1       13            NA        B      1
## 161 147    10             1       13            NA        B      1
## 162 124    13             1       13            NA        B      1
## 163 157    10             1       13            NA        B      1
## 164 194     9             0       13             0        B      1
## 165 109     9             1       13            NA        B      1
## 166   3    12             1       13            NA        B      1
## 167 101    12             1       13            NA        B      1
## 168  59    10             1       13            NA        B      1
## 169 289    11             0       13             0        B      1
## 170  43    13             1       17            NA        B      1
## 171 253    12             0       17             0        B      1
## 172 240    13             1       17            NA        B      1
## 173 137    15             0       17             1        B      1
## 174 372    14             1       17            NA        B      1
## 175  20    12             0       17             0        B      1
## 176 290    10             1       17            NA        B      1
## 177 263    13             1       17            NA        B      1
## 178  22    10             1       17            NA        B      1
## 179  75    10             0       17             0        B      1
## 180 309    13             1       17            NA        B      1
## 181 284    11             0       17             0        B      1
## 182 162    12             1       17            NA        B      1
## 183 102    13             1       17            NA        B      1
## 184 285    12             1       18            NA        B      0
## 185 373    13             1       18            NA        B      0
## 186  92     9             0       18             0        B      0
## 187 344    13             1       18            NA        B      0
## 188 227    13             1       18            NA        B      0
## 189 186    13             0       18             0        B      0
## 190 136    11             1       18            NA        B      0
## 191 232    12             1       18            NA        B      0
## 192 297    11             0       18            NA        B      0
## 193 351    14             1       18            NA        B      0
## 194 346    13             0       18             0        B      0
## 195 149    12             1       18            NA        B      0
## 196 180    14             0       18            NA        B      0
## 197 239    13             1       18            NA        B      0
## 198 307    10             1       18            NA        B      0
## 199  23    10             1       18            NA        B      0
## 200 280    11             1        4            NA        B      0
## 201   2    10             1        4            NA        B      0
## 202 165    12             1        4            NA        B      0
## 203 114    14             1        4            NA        B      0
## 204 242    12             1        4            NA        B      0
## 205 362    14             0        4             1        B      0
## 206 177     9             0        4             0        B      0
## 207 271     9             1        4            NA        B      0
## 208 117    13             1        4            NA        B      0
## 209 235    11             1        4            NA        B      0
## 210   1    14             0        4             0        B      0
## 211 163    12             1        4            NA        B      0
## 212 255    15             1        4            NA        B      0
## 213  73     9             0        4             0        B      0
## 214 223    11             1        4            NA        B      0
## 215 252    11             1        4            NA        B      0
## 216 106    11             0       12             0        B      0
## 217  88    14             1       12            NA        B      0
## 218  51    14             1       12            NA        B      0
## 219  52     9             1       12            NA        B      0
## 220 196    13             1       12            NA        B      0
## 221 350    14             1       12            NA        B      0
## 222 191     9             1       12            NA        B      0
## 223 260    14             0       12            NA        B      0
## 224  97    14             1       12            NA        B      0
## 225 352    10             1       12            NA        B      0
## 226 360    11             1       12            NA        B      0
## 227 116    10             1       12            NA        B      0
## 228 173    12             1        9            NA        B      1
## 229 221    11             1        9            NA        B      1
## 230  76    14             0        9             1        B      1
## 231  93    15             1        9            NA        B      1
## 232  10    12             1        9            NA        B      1
## 233 257    13             1        9            NA        B      1
## 234 139    11             1        9            NA        B      1
## 235 111     9             0        9            NA        B      1
## 236 254    13             0        9             0        B      1
## 237   7    14             1        9            NA        B      1
## 238  86    13             1        9            NA        B      1
## 239 199    13             1        9            NA        B      1
## 240 168    13             1        9            NA        B      1
## 241  48    14             1        9            NA        B      1
## 242  14    12             1        9            NA        B      1
## 243 355    14             1        9            NA        B      1
## 244 126    14             1        9            NA        B      1
## 245 343    14             1        9            NA        B      1
## 246 321    11             1        9            NA        B      1
## 247 152    10             1        9            NA        B      1
## 248  72    13             0        9             1        B      1
## 249 357    10             1        9            NA        B      1
## 250 216    13             1        9            NA        B      1
## 251 181     9             0        9            NA        B      1
## 252  53    10             0        9             0        B      1
## 253 303    15             1        9            NA        B      1
## 254 369     9             0        9             0        B      1
## 255  96     9             1        9            NA        B      1
## 256  24    13             1        9            NA        B      1
## 257 288    15             1        9            NA        B      1
## 258 328    12             1       21            NA        B      0
## 259 313    11             1       21            NA        B      0
## 260 306    14             1       21            NA        B      0
## 261 237     9             0       21             1        B      0
## 262 318    11             1       21            NA        B      0
## 263 356    14             1       21            NA        B      0
## 264 247    15             0       21             0        B      0
## 265 202    10             0       21             0        B      0
## 266  13    12             0       21             0        B      0
## 267  49    10             1       21            NA        B      0
## 268 325    14             1       21            NA        B      0
## 269 122    13             1       21            NA        B      0
## 270 176     9             0       21             0        B      0
## 271  25    10             1       21            NA        B      0
## 272 129     9             1        8            NA        B      1
## 273 248    11             1        8            NA        B      1
## 274 174    13             0        8             0        B      1
## 275 269    10             0        8             0        B      1
## 276 200    13             1        8            NA        B      1
## 277 100    15             1        8            NA        B      1
## 278  54    11             1        8            NA        B      1
## 279 250    14             1        8            NA        B      1
## 280 132    13             1        8            NA        B      1
## 281 188    12             0        8             1        B      1
## 282 135    14             1        8            NA        B      1
## 283 305    12             1        8            NA        B      1
## 284 229    13             1        8            NA        B      1
## 285 184    11             1        8            NA        B      1
## 286 327    13             0        8             0        B      1
## 287 140    15             0       22             0        B      1
## 288 338    10             0       22             0        B      1
## 289  37    13             1       22            NA        B      1
## 290  98    14             0       22             1        B      1
## 291  26    11             1       22            NA        B      1
## 292  94    11             1       22            NA        B      1
## 293 183    13             1       22            NA        B      1
## 294  69     9             1       22            NA        B      1
## 295 218    14             1       22            NA        B      1
## 296   9    14             1       22            NA        B      1
## 297 205    13             0       22             1        B      1
## 298 144    12             1       11            NA        B      0
## 299 245    13             0       11             1        B      0
## 300 145    13             1       11            NA        B      0
## 301 151    15             1       11            NA        B      0
## 302  42    11             1       11            NA        B      0
## 303  62     9             1       11            NA        B      0
## 304 192    11             1       11            NA        B      0
## 305  11    15             0       11             1        B      0
## 306 332    14             1       11            NA        B      0
## 307 323     9             1       11            NA        B      0
## 308 103    14             1       11            NA        B      0
## 309 266    11             1       11            NA        B      0
## 310 341    15             1       11            NA        B      0
## 311 201    14             1       11            NA        B      0
## 312 178    13             1       11            NA        B      0
## 313 256    12             1       11            NA        B      0
## 314 238     9             1       11            NA        B      0
## 315  41    14             1       11            NA        B      0
## 316 353    12             1       11            NA        B      0
## 317 249    11             0       11            NA        B      0
## 318 211    11             0       11             0        B      0
## 319 367    11             0       11             1        B      0
## 320 225    15             1       11            NA        B      0
## 321 226    14             0       11             1        B      0
## 322 138    10             1       11            NA        B      0
## 323 326    11             1       11            NA        B      0
## 324  33    10             1       11            NA        B      0
## 325 244     9             0       11             0        B      0
## 326 264     9             0       11             1        B      0
## 327 294    14             1       11            NA        B      0
## 328  58    13             1       11            NA        B      0
## 329 273    11             0       11             0        B      0
## 330  79    15             1       11            NA        B      0
## 331 198     9             0       11             1        B      0
## 332  55    11             0       11             1        B      0
## 333  65    14             0       11             0        B      0
## 334 301     9             0       11             0        B      0
## 335 107    11             1       11            NA        B      0
## 336  83     9             1       11            NA        B      0
## 337 131    10             1       16            NA        B      1
## 338 234    12             1       16            NA        B      1
## 339 342    13             0       16             0        B      1
## 340 210    11             1       16            NA        B      1
## 341 268    13             1       16            NA        B      1
## 342 212    14             1       16            NA        B      1
## 343 123    15             1       16            NA        B      1
## 344  16    12             0       16             1        B      1
## 345 154    12             1       16            NA        B      1
## 346  44     9             0       16             1        B      1
## 347 277    11             1       16            NA        B      1
## 348 230    12             0       16             1        B      1
## 349  40    11             1       16            NA        B      1
## 350 363    12             0       16            NA        B      1
## 351  28    15             0       16             0        B      1
## 352 213    12             0       16             0        B      1
## 353 153    13             1       16            NA        B      1
## 354 195    11             0       16             1        B      1
## 355 292    15             0       16             0        B      1
## 356 125    13             1       16            NA        B      1
## 357 222    13             1       16            NA        B      1
## 358 259    10             0       16             0        B      1
## 359 340    14             1       16            NA        B      1
## 360 167    13             1       16            NA        B      1
## 361  21    14             1       16            NA        B      1
## 362 190    11             1       16            NA        B      1
## 363  35    10             0       16             0        B      1
## 364 150    13             1       16            NA        B      1
## 365 233    10             0       16             1        B      1
## 366 193    14             0       16             0        B      1
## 367 169    15             1       16            NA        B      1
## 368   6    13             1       16            NA        B      1
## 369 231    12             1       16            NA        B      1
## 370  32    13             1       16            NA        B      1
## 371  68    13             0       16             0        B      1
## 372 270    11             1       16            NA        B      1

Now join individuals/clinics to districts

  datR1 %>%
    full_join(datR2, 
              by = "ID", 
              suffix = c(".r1", ".r2")) %>%
    left_join(clinics, by="clinicID") %>%
    left_join(districts, by="district")
##      ID c.age p.knowsHIV.r1 clinicID p.knowsHIV.r2 district public    pop
## 1   118     9             1        2            NA        A      1 150000
## 2    60    15             1        2            NA        A      1 150000
## 3   311    10             1        2            NA        A      1 150000
## 4   347     9             0        2            NA        A      1 150000
## 5   315    10             0        2             0        A      1 150000
## 6    99    15             1        2            NA        A      1 150000
## 7   146    11             0        2             0        A      1 150000
## 8   339    13             1        2            NA        A      1 150000
## 9   228    10             1        2            NA        A      1 150000
## 10  368    12             1        2            NA        A      1 150000
## 11   29    10             1        2            NA        A      1 150000
## 12  286    15             1        7            NA        A      1 150000
## 13  220    11             0        7             1        A      1 150000
## 14  166    13             0        7             0        A      1 150000
## 15  278    14             0        7             0        A      1 150000
## 16  302    13             1        7            NA        A      1 150000
## 17  361    14             1        7            NA        A      1 150000
## 18  120    14             1        7            NA        A      1 150000
## 19   70    14             0        7             0        A      1 150000
## 20   91    15             1        7            NA        A      1 150000
## 21  337    12             1        7            NA        A      1 150000
## 22  335    14             1        7            NA        A      1 150000
## 23   84    15             1        7            NA        A      1 150000
## 24  272    15             1        7            NA        A      1 150000
## 25  300    12             0        7             1        A      1 150000
## 26  204    15             0        7             0        A      1 150000
## 27  164    11             1        7            NA        A      1 150000
## 28  345    13             0        7             1        A      1 150000
## 29   80    14             0        7             0        A      1 150000
## 30  333    10             0        7             1        A      1 150000
## 31  161    12             0        7             1        A      1 150000
## 32  156    15             1       19            NA        A      1 150000
## 33  208    13             1       19            NA        A      1 150000
## 34   39    13             0       19             0        A      1 150000
## 35   95    12             1       19            NA        A      1 150000
## 36  182    13             1       19            NA        A      1 150000
## 37  299     9             0       19             1        A      1 150000
## 38  243    13             1       19            NA        A      1 150000
## 39  207    12             1       19            NA        A      1 150000
## 40   18    11             1       19            NA        A      1 150000
## 41  358    12             1       19            NA        A      1 150000
## 42  143    15             1       10            NA        A      1 150000
## 43   27    11             0       10            NA        A      1 150000
## 44  115    10             1       10            NA        A      1 150000
## 45  219    13             0       10             0        A      1 150000
## 46  348    12             0       10             1        A      1 150000
## 47  119    14             1       10            NA        A      1 150000
## 48   47    10             1       10            NA        A      1 150000
## 49   38    12             1       10            NA        A      1 150000
## 50  314    13             0       10             1        A      1 150000
## 51  134    15             1       10            NA        A      1 150000
## 52   63    14             1       10            NA        A      1 150000
## 53  110    13             1       10            NA        A      1 150000
## 54  155    11             0       10             0        A      1 150000
## 55  330    14             1       10            NA        A      1 150000
## 56  171    13             0       10             0        A      1 150000
## 57  316    13             1       10            NA        A      1 150000
## 58  334    11             1       10            NA        A      1 150000
## 59  197    14             0       10             1        A      1 150000
## 60  267    11             1       10            NA        A      1 150000
## 61  236    13             1       10            NA        A      1 150000
## 62  281    15             1        3            NA        A      1 150000
## 63  241    11             1        3            NA        A      1 150000
## 64  298    12             1        3            NA        A      1 150000
## 65  142    11             1        3            NA        A      1 150000
## 66   56    13             1        3            NA        A      1 150000
## 67  127    12             1        3            NA        A      1 150000
## 68  179    13             1        3            NA        A      1 150000
## 69  159    12             1        3            NA        A      1 150000
## 70   46    10             0        3             1        A      1 150000
## 71  308    13             1        3            NA        A      1 150000
## 72  262    14             1        3            NA        A      1 150000
## 73  215    13             1        3            NA        A      1 150000
## 74  310    14             1        3            NA        A      1 150000
## 75  158    13             0        3             0        A      1 150000
## 76  291    14             1       15            NA        A      1 150000
## 77  148    12             0       15             0        A      1 150000
## 78   50    11             0       15             0        A      1 150000
## 79    8    14             1       15            NA        A      1 150000
## 80  214    13             0       15             0        A      1 150000
## 81  251    12             1       15            NA        A      1 150000
## 82  320    15             1       15            NA        A      1 150000
## 83  296    11             0       15             1        A      1 150000
## 84  113    14             1       15            NA        A      1 150000
## 85   15    11             0       15             0        A      1 150000
## 86  295    14             1       20            NA        A      0 150000
## 87  371    10             0       20             0        A      0 150000
## 88  324    12             0       20             0        A      0 150000
## 89   31    13             0       20             0        A      0 150000
## 90  133    14             1       20            NA        A      0 150000
## 91  172    10             0       20             0        A      0 150000
## 92   85    13             1       20            NA        A      0 150000
## 93  175    14             1       20            NA        A      0 150000
## 94  185     9             0       20            NA        A      0 150000
## 95    5    12             1       20            NA        A      0 150000
## 96   45    14             1       20            NA        A      0 150000
## 97  217    11             0        6             0        A      0 150000
## 98  349    11             1        6            NA        A      0 150000
## 99   67    15             1        6            NA        A      0 150000
## 100 370    13             0        6             0        A      0 150000
## 101 130    11             1        6            NA        A      0 150000
## 102 187    15             0        6             0        A      0 150000
## 103 274    14             1        6            NA        A      0 150000
## 104  61    11             0        6             0        A      0 150000
## 105 279    15             1        6            NA        A      0 150000
## 106 287     9             1        6            NA        A      0 150000
## 107 209    10             1        1            NA        A      1 150000
## 108 128    12             0        1             0        A      1 150000
## 109 329    10             0        1             1        A      1 150000
## 110 276    12             0        1             0        A      1 150000
## 111 336    10             1        1            NA        A      1 150000
## 112 108    11             0        1             0        A      1 150000
## 113  77    14             0        1             0        A      1 150000
## 114  82    12             1        1            NA        A      1 150000
## 115 121    12             0        1             0        A      1 150000
## 116 331    15             1        1            NA        A      1 150000
## 117 141    11             1        1            NA        A      1 150000
## 118  90    11             0        1             0        A      1 150000
## 119 319    10             1        1            NA        A      1 150000
## 120 317     9             0        1             1        A      1 150000
## 121 354    14             1        1            NA        A      1 150000
## 122 224    11             0        1             1        A      1 150000
## 123 365    13             1        1            NA        A      1 150000
## 124 246    13             0        1             1        A      1 150000
## 125 275    12             0        1             1        A      1 150000
## 126 283    12             1        1            NA        A      1 150000
## 127 170    12             1        1            NA        A      1 150000
## 128  89    13             1        1            NA        A      1 150000
## 129  78    12             0        1             1        A      1 150000
## 130 359    12             0       14             0        A      0 150000
## 131  34    14             1       14            NA        A      0 150000
## 132  36    10             1       14            NA        A      0 150000
## 133 189    12             1       14            NA        A      0 150000
## 134  17    12             1       14            NA        A      0 150000
## 135  30    11             1       14            NA        A      0 150000
## 136 364    10             0       14             0        A      0 150000
## 137 203    11             1       14            NA        A      0 150000
## 138   4    12             1       14            NA        A      0 150000
## 139 322    13             1       14            NA        A      0 150000
## 140 112    10             1       14            NA        A      0 150000
## 141 304    11             1       14            NA        A      0 150000
## 142  74    14             1       14            NA        A      0 150000
## 143  66    12             0       14             1        A      0 150000
## 144 160    10             1       14            NA        A      0 150000
## 145 104    13             1       14            NA        A      0 150000
## 146  19     9             0       14             0        A      0 150000
## 147  12    14             1       14            NA        A      0 150000
## 148 312    12             1       14            NA        A      0 150000
## 149 258    11             1       14            NA        A      0 150000
## 150 265    14             1       14            NA        A      0 150000
## 151  81    14             1       14            NA        A      0 150000
## 152 293    12             1       14            NA        A      0 150000
## 153  87    10             1       14            NA        A      0 150000
## 154 261    11             1       14            NA        A      0 150000
## 155 366    12             0       14             1        A      0 150000
## 156  71    11             0       14             1        A      0 150000
## 157 282    11             0       13             1        B      1 250000
## 158 105    13             0       13            NA        B      1 250000
## 159 206    10             1       13            NA        B      1 250000
## 160  57     9             1       13            NA        B      1 250000
## 161 147    10             1       13            NA        B      1 250000
## 162 124    13             1       13            NA        B      1 250000
## 163 157    10             1       13            NA        B      1 250000
## 164 194     9             0       13             0        B      1 250000
## 165 109     9             1       13            NA        B      1 250000
## 166   3    12             1       13            NA        B      1 250000
## 167 101    12             1       13            NA        B      1 250000
## 168  59    10             1       13            NA        B      1 250000
## 169 289    11             0       13             0        B      1 250000
## 170  43    13             1       17            NA        B      1 250000
## 171 253    12             0       17             0        B      1 250000
## 172 240    13             1       17            NA        B      1 250000
## 173 137    15             0       17             1        B      1 250000
## 174 372    14             1       17            NA        B      1 250000
## 175  20    12             0       17             0        B      1 250000
## 176 290    10             1       17            NA        B      1 250000
## 177 263    13             1       17            NA        B      1 250000
## 178  22    10             1       17            NA        B      1 250000
## 179  75    10             0       17             0        B      1 250000
## 180 309    13             1       17            NA        B      1 250000
## 181 284    11             0       17             0        B      1 250000
## 182 162    12             1       17            NA        B      1 250000
## 183 102    13             1       17            NA        B      1 250000
## 184 285    12             1       18            NA        B      0 250000
## 185 373    13             1       18            NA        B      0 250000
## 186  92     9             0       18             0        B      0 250000
## 187 344    13             1       18            NA        B      0 250000
## 188 227    13             1       18            NA        B      0 250000
## 189 186    13             0       18             0        B      0 250000
## 190 136    11             1       18            NA        B      0 250000
## 191 232    12             1       18            NA        B      0 250000
## 192 297    11             0       18            NA        B      0 250000
## 193 351    14             1       18            NA        B      0 250000
## 194 346    13             0       18             0        B      0 250000
## 195 149    12             1       18            NA        B      0 250000
## 196 180    14             0       18            NA        B      0 250000
## 197 239    13             1       18            NA        B      0 250000
## 198 307    10             1       18            NA        B      0 250000
## 199  23    10             1       18            NA        B      0 250000
## 200 280    11             1        4            NA        B      0 250000
## 201   2    10             1        4            NA        B      0 250000
## 202 165    12             1        4            NA        B      0 250000
## 203 114    14             1        4            NA        B      0 250000
## 204 242    12             1        4            NA        B      0 250000
## 205 362    14             0        4             1        B      0 250000
## 206 177     9             0        4             0        B      0 250000
## 207 271     9             1        4            NA        B      0 250000
## 208 117    13             1        4            NA        B      0 250000
## 209 235    11             1        4            NA        B      0 250000
## 210   1    14             0        4             0        B      0 250000
## 211 163    12             1        4            NA        B      0 250000
## 212 255    15             1        4            NA        B      0 250000
## 213  73     9             0        4             0        B      0 250000
## 214 223    11             1        4            NA        B      0 250000
## 215 252    11             1        4            NA        B      0 250000
## 216 106    11             0       12             0        B      0 250000
## 217  88    14             1       12            NA        B      0 250000
## 218  51    14             1       12            NA        B      0 250000
## 219  52     9             1       12            NA        B      0 250000
## 220 196    13             1       12            NA        B      0 250000
## 221 350    14             1       12            NA        B      0 250000
## 222 191     9             1       12            NA        B      0 250000
## 223 260    14             0       12            NA        B      0 250000
## 224  97    14             1       12            NA        B      0 250000
## 225 352    10             1       12            NA        B      0 250000
## 226 360    11             1       12            NA        B      0 250000
## 227 116    10             1       12            NA        B      0 250000
## 228 173    12             1        9            NA        B      1 250000
## 229 221    11             1        9            NA        B      1 250000
## 230  76    14             0        9             1        B      1 250000
## 231  93    15             1        9            NA        B      1 250000
## 232  10    12             1        9            NA        B      1 250000
## 233 257    13             1        9            NA        B      1 250000
## 234 139    11             1        9            NA        B      1 250000
## 235 111     9             0        9            NA        B      1 250000
## 236 254    13             0        9             0        B      1 250000
## 237   7    14             1        9            NA        B      1 250000
## 238  86    13             1        9            NA        B      1 250000
## 239 199    13             1        9            NA        B      1 250000
## 240 168    13             1        9            NA        B      1 250000
## 241  48    14             1        9            NA        B      1 250000
## 242  14    12             1        9            NA        B      1 250000
## 243 355    14             1        9            NA        B      1 250000
## 244 126    14             1        9            NA        B      1 250000
## 245 343    14             1        9            NA        B      1 250000
## 246 321    11             1        9            NA        B      1 250000
## 247 152    10             1        9            NA        B      1 250000
## 248  72    13             0        9             1        B      1 250000
## 249 357    10             1        9            NA        B      1 250000
## 250 216    13             1        9            NA        B      1 250000
## 251 181     9             0        9            NA        B      1 250000
## 252  53    10             0        9             0        B      1 250000
## 253 303    15             1        9            NA        B      1 250000
## 254 369     9             0        9             0        B      1 250000
## 255  96     9             1        9            NA        B      1 250000
## 256  24    13             1        9            NA        B      1 250000
## 257 288    15             1        9            NA        B      1 250000
## 258 328    12             1       21            NA        B      0 250000
## 259 313    11             1       21            NA        B      0 250000
## 260 306    14             1       21            NA        B      0 250000
## 261 237     9             0       21             1        B      0 250000
## 262 318    11             1       21            NA        B      0 250000
## 263 356    14             1       21            NA        B      0 250000
## 264 247    15             0       21             0        B      0 250000
## 265 202    10             0       21             0        B      0 250000
## 266  13    12             0       21             0        B      0 250000
## 267  49    10             1       21            NA        B      0 250000
## 268 325    14             1       21            NA        B      0 250000
## 269 122    13             1       21            NA        B      0 250000
## 270 176     9             0       21             0        B      0 250000
## 271  25    10             1       21            NA        B      0 250000
## 272 129     9             1        8            NA        B      1 250000
## 273 248    11             1        8            NA        B      1 250000
## 274 174    13             0        8             0        B      1 250000
## 275 269    10             0        8             0        B      1 250000
## 276 200    13             1        8            NA        B      1 250000
## 277 100    15             1        8            NA        B      1 250000
## 278  54    11             1        8            NA        B      1 250000
## 279 250    14             1        8            NA        B      1 250000
## 280 132    13             1        8            NA        B      1 250000
## 281 188    12             0        8             1        B      1 250000
## 282 135    14             1        8            NA        B      1 250000
## 283 305    12             1        8            NA        B      1 250000
## 284 229    13             1        8            NA        B      1 250000
## 285 184    11             1        8            NA        B      1 250000
## 286 327    13             0        8             0        B      1 250000
## 287 140    15             0       22             0        B      1 250000
## 288 338    10             0       22             0        B      1 250000
## 289  37    13             1       22            NA        B      1 250000
## 290  98    14             0       22             1        B      1 250000
## 291  26    11             1       22            NA        B      1 250000
## 292  94    11             1       22            NA        B      1 250000
## 293 183    13             1       22            NA        B      1 250000
## 294  69     9             1       22            NA        B      1 250000
## 295 218    14             1       22            NA        B      1 250000
## 296   9    14             1       22            NA        B      1 250000
## 297 205    13             0       22             1        B      1 250000
## 298 144    12             1       11            NA        B      0 250000
## 299 245    13             0       11             1        B      0 250000
## 300 145    13             1       11            NA        B      0 250000
## 301 151    15             1       11            NA        B      0 250000
## 302  42    11             1       11            NA        B      0 250000
## 303  62     9             1       11            NA        B      0 250000
## 304 192    11             1       11            NA        B      0 250000
## 305  11    15             0       11             1        B      0 250000
## 306 332    14             1       11            NA        B      0 250000
## 307 323     9             1       11            NA        B      0 250000
## 308 103    14             1       11            NA        B      0 250000
## 309 266    11             1       11            NA        B      0 250000
## 310 341    15             1       11            NA        B      0 250000
## 311 201    14             1       11            NA        B      0 250000
## 312 178    13             1       11            NA        B      0 250000
## 313 256    12             1       11            NA        B      0 250000
## 314 238     9             1       11            NA        B      0 250000
## 315  41    14             1       11            NA        B      0 250000
## 316 353    12             1       11            NA        B      0 250000
## 317 249    11             0       11            NA        B      0 250000
## 318 211    11             0       11             0        B      0 250000
## 319 367    11             0       11             1        B      0 250000
## 320 225    15             1       11            NA        B      0 250000
## 321 226    14             0       11             1        B      0 250000
## 322 138    10             1       11            NA        B      0 250000
## 323 326    11             1       11            NA        B      0 250000
## 324  33    10             1       11            NA        B      0 250000
## 325 244     9             0       11             0        B      0 250000
## 326 264     9             0       11             1        B      0 250000
## 327 294    14             1       11            NA        B      0 250000
## 328  58    13             1       11            NA        B      0 250000
## 329 273    11             0       11             0        B      0 250000
## 330  79    15             1       11            NA        B      0 250000
## 331 198     9             0       11             1        B      0 250000
## 332  55    11             0       11             1        B      0 250000
## 333  65    14             0       11             0        B      0 250000
## 334 301     9             0       11             0        B      0 250000
## 335 107    11             1       11            NA        B      0 250000
## 336  83     9             1       11            NA        B      0 250000
## 337 131    10             1       16            NA        B      1 250000
## 338 234    12             1       16            NA        B      1 250000
## 339 342    13             0       16             0        B      1 250000
## 340 210    11             1       16            NA        B      1 250000
## 341 268    13             1       16            NA        B      1 250000
## 342 212    14             1       16            NA        B      1 250000
## 343 123    15             1       16            NA        B      1 250000
## 344  16    12             0       16             1        B      1 250000
## 345 154    12             1       16            NA        B      1 250000
## 346  44     9             0       16             1        B      1 250000
## 347 277    11             1       16            NA        B      1 250000
## 348 230    12             0       16             1        B      1 250000
## 349  40    11             1       16            NA        B      1 250000
## 350 363    12             0       16            NA        B      1 250000
## 351  28    15             0       16             0        B      1 250000
## 352 213    12             0       16             0        B      1 250000
## 353 153    13             1       16            NA        B      1 250000
## 354 195    11             0       16             1        B      1 250000
## 355 292    15             0       16             0        B      1 250000
## 356 125    13             1       16            NA        B      1 250000
## 357 222    13             1       16            NA        B      1 250000
## 358 259    10             0       16             0        B      1 250000
## 359 340    14             1       16            NA        B      1 250000
## 360 167    13             1       16            NA        B      1 250000
## 361  21    14             1       16            NA        B      1 250000
## 362 190    11             1       16            NA        B      1 250000
## 363  35    10             0       16             0        B      1 250000
## 364 150    13             1       16            NA        B      1 250000
## 365 233    10             0       16             1        B      1 250000
## 366 193    14             0       16             0        B      1 250000
## 367 169    15             1       16            NA        B      1 250000
## 368   6    13             1       16            NA        B      1 250000
## 369 231    12             1       16            NA        B      1 250000
## 370  32    13             1       16            NA        B      1 250000
## 371  68    13             0       16             0        B      1 250000
## 372 270    11             1       16            NA        B      1 250000

References

Wickham, Hadley, and Garrett Grolemund. 2017. R for Data Science. O’Reilly. http://r4ds.had.co.nz/.